{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pandas import DataFrame,Series\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "outputs": [
    {
     "data": {
      "text/plain": "    color  price\n0   green      4\n1   green      5\n2  yellow      3\n3    blue      2\n4    blue      7\n5  yellow      8\n6  yellow      9",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>color</th>\n      <th>price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>green</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>green</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>yellow</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>blue</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>blue</td>\n      <td>7</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>yellow</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>yellow</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\n",
    "        'color': ['green', 'green', 'yellow', 'blue', 'blue', 'yellow', 'yellow'],\n",
    "        'price': [4, 5, 3, 2, 7, 8, 9]\n",
    "    }\n",
    ")\n",
    "df\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "outputs": [
    {
     "data": {
      "text/plain": "{'blue': [3, 4], 'green': [0, 1], 'yellow': [2, 5, 6]}"
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(by=\"color\").groups\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "outputs": [
    {
     "data": {
      "text/plain": "           price\ncolor           \nblue    4.500000\ngreen   4.500000\nyellow  6.666667",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>price</th>\n    </tr>\n    <tr>\n      <th>color</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>blue</th>\n      <td>4.500000</td>\n    </tr>\n    <tr>\n      <th>green</th>\n      <td>4.500000</td>\n    </tr>\n    <tr>\n      <th>yellow</th>\n      <td>6.666667</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(by=\"color\").sum()\n",
    "df.groupby(by=\"color\").count()\n",
    "\n",
    "df1 =df.groupby(by=\"color\").mean()\n",
    "df1"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "outputs": [
    {
     "data": {
      "text/plain": "price    4.5\nName: blue, dtype: float64"
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.iloc[0]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "outputs": [
    {
     "data": {
      "text/plain": "  item color  weight  price\n0   萝卜     白      10   0.99\n1   白菜     青      20   1.99\n2   辣椒     红      10   2.99\n3   冬瓜     白      10   3.99\n4   萝卜     青      30   4.00\n5   白菜     红      40   5.00\n6   辣椒     白      50   6.00\n7   冬瓜     青      60   7.00",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>item</th>\n      <th>color</th>\n      <th>weight</th>\n      <th>price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>萝卜</td>\n      <td>白</td>\n      <td>10</td>\n      <td>0.99</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>白菜</td>\n      <td>青</td>\n      <td>20</td>\n      <td>1.99</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>辣椒</td>\n      <td>红</td>\n      <td>10</td>\n      <td>2.99</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>冬瓜</td>\n      <td>白</td>\n      <td>10</td>\n      <td>3.99</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>萝卜</td>\n      <td>青</td>\n      <td>30</td>\n      <td>4.00</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>白菜</td>\n      <td>红</td>\n      <td>40</td>\n      <td>5.00</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>辣椒</td>\n      <td>白</td>\n      <td>50</td>\n      <td>6.00</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>冬瓜</td>\n      <td>青</td>\n      <td>60</td>\n      <td>7.00</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ddd = pd.DataFrame(\n",
    "    data={\n",
    "        \"item\": [\"萝卜\",\"白菜\",\"辣椒\",\"冬瓜\",\"萝卜\",\"白菜\",\"辣椒\",\"冬瓜\"],\n",
    "        'color':[\"白\",\"青\",\"红\",\"白\",\"青\",\"红\",\"白\",\"青\"],\n",
    "        'weight': [10,20,10,10,30,40,50,60],\n",
    "        'price': [0.99, 1.99, 2.99, 3.99, 4, 5, 6,7]\n",
    "    }\n",
    ")\n",
    "ddd\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "outputs": [
    {
     "data": {
      "text/plain": "10.98"
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ddd.groupby(by=\"color\")[\"price\"].sum().loc[\"白\"]\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "outputs": [
    {
     "data": {
      "text/plain": "  item color  weight  price\n0   萝卜     白      10   0.99\n4   萝卜     青      30   4.00",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>item</th>\n      <th>color</th>\n      <th>weight</th>\n      <th>price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>萝卜</td>\n      <td>白</td>\n      <td>10</td>\n      <td>0.99</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>萝卜</td>\n      <td>青</td>\n      <td>30</td>\n      <td>4.00</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ddd.query(\"item == '萝卜'\")\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "outputs": [
    {
     "data": {
      "text/plain": "weight    40\nName: 萝卜, dtype: int64"
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = ddd.groupby('item')[['weight']].sum()\n",
    "df3 = df1.loc[\"萝卜\"]\n",
    "df3"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "outputs": [
    {
     "data": {
      "text/plain": "price    2.495\nName: 萝卜, dtype: float64"
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = ddd.groupby('item')[['price']].mean()\n",
    "df2 = df1.loc[\"萝卜\"]\n",
    "df2"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "outputs": [
    {
     "data": {
      "text/plain": "Empty DataFrame\nColumns: [weight, 萝卜]\nIndex: []",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>weight</th>\n      <th>萝卜</th>\n    </tr>\n  </thead>\n  <tbody>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "outputs": [
    {
     "data": {
      "text/plain": "      weight\nitem        \n冬瓜        70\n白菜        60\n萝卜        40\n辣椒        60",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>weight</th>\n    </tr>\n    <tr>\n      <th>item</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>冬瓜</th>\n      <td>70</td>\n    </tr>\n    <tr>\n      <th>白菜</th>\n      <td>60</td>\n    </tr>\n    <tr>\n      <th>萝卜</th>\n      <td>40</td>\n    </tr>\n    <tr>\n      <th>辣椒</th>\n      <td>60</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": "      price\nitem       \n冬瓜    5.495\n白菜    3.495\n萝卜    2.495\n辣椒    4.495",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>price</th>\n    </tr>\n    <tr>\n      <th>item</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>冬瓜</th>\n      <td>5.495</td>\n    </tr>\n    <tr>\n      <th>白菜</th>\n      <td>3.495</td>\n    </tr>\n    <tr>\n      <th>萝卜</th>\n      <td>2.495</td>\n    </tr>\n    <tr>\n      <th>辣椒</th>\n      <td>4.495</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df1 = ddd.groupby('item')[['weight']].sum()\n",
    "df2= ddd.groupby('item')[['price']].mean()\n",
    "display(df1,df2)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "outputs": [
    {
     "data": {
      "text/plain": "      weight  price\nitem               \n冬瓜        70  5.495\n白菜        60  3.495\n萝卜        40  2.495\n辣椒        60  4.495",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>weight</th>\n      <th>price</th>\n    </tr>\n    <tr>\n      <th>item</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>冬瓜</th>\n      <td>70</td>\n      <td>5.495</td>\n    </tr>\n    <tr>\n      <th>白菜</th>\n      <td>60</td>\n      <td>3.495</td>\n    </tr>\n    <tr>\n      <th>萝卜</th>\n      <td>40</td>\n      <td>2.495</td>\n    </tr>\n    <tr>\n      <th>辣椒</th>\n      <td>60</td>\n      <td>4.495</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.merge(df2,left_index=True,right_index=True)\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}